package com.mobilesales.android.database;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.util.Log;

import com.mobilesales.android.model.dictionary.Dictionary;
import com.mobilesales.android.model.dictionary.KeyValuePair;
import com.mobilesales.android.model.params.Param;
import com.mobilesales.android.model.products.Category;
import com.mobilesales.android.model.products.Product;
import com.mobilesales.android.model.products.ProductImage;
import com.mobilesales.android.model.products.Tax;
import com.mobilesales.android.model.products.Unit;

public class ProductManager extends ManagerBase {

	public ProductManager(Context c) {
		super(c);
	}

	public List<Product> GetProductList() {
		try {

			String query = "SELECT _id" + " FROM Products";
			Cursor c = db.rawQuery(query, null);

			List<Product> result = new ArrayList<Product>();
			if (!c.moveToFirst())
				return result;

			List<Integer> tmp = new ArrayList<Integer>();
			while (!c.isAfterLast()) {
				tmp.add(c.getInt(c.getColumnIndex("_id")));
				c.moveToNext();
			}
			c.close();
			for (int id : tmp)
				result.add(GetProduct(id));
			return result;
		} catch (SQLException sqle) {
			Log.d(this.getClass().getName(), sqle.getMessage(),sqle);
			throw sqle;
		} catch (Exception e) {
			Log.d(this.getClass().getName(), e.getMessage(), e);
		}

		return null;
	}

	public Product GetProduct(int productId) {
		try {

			String query = "SELECT _id, TaxId, UnitId, Sku, Name, Description, Price, Quantity, MinQuantity, OrderQuantity, Spread, Symb, MarketingOnly"
					+ " FROM Products" + " WHERE _id = " + productId;
			Cursor c = db.rawQuery(query, null);

			if (!c.moveToFirst())
				return null;

			Product result = new Product();
			result.Id = c.getInt(c.getColumnIndex("_id"));
			result.Name = c.getString(c.getColumnIndex("Name"));
			result.TaxId = c.getInt(c.getColumnIndex("TaxId"));
			result.UnitId = c.getInt(c.getColumnIndex("UnitId"));
			result.Sku = c.getString(c.getColumnIndex("Sku"));
			result.Description = c.getString(c.getColumnIndex("Description"));
			result.Price = c.getDouble(c.getColumnIndex("Price"));
			result.Quantity = c.getDouble(c.getColumnIndex("Quantity"));
			result.MinQuantity = c.getDouble(c.getColumnIndex("MinQuantity"));
			result.OrderQuantity = c.getDouble(c
					.getColumnIndex("OrderQuantity"));
			result.Spread = c.getDouble(c.getColumnIndex("Spread"));
			result.Symbol = c.getString(c.getColumnIndex("Symb"));
			result.IsMarketingOnlyItem = c.getInt(c
					.getColumnIndex("MarketingOnly")) == 1 ? true : false;

			c.close();

			result.Params = GetProductParams(result.Id);
			result.Categories = GetProductCategories(result.Id);
			result.Images = GetProductImages(result.Id);
			result.Tax = GetTax(result.TaxId);
			result.Unit = GetUnit(result.UnitId);
			return result;
		} catch (SQLException sqle) {
			Log.d(this.getClass().getName(), sqle.getMessage(),sqle);
			throw sqle;
		} catch (Exception e) {
			Log.d(this.getClass().getName(), e.getMessage(), e);
		}

		return null;
	}

	public boolean RemoveProduct(int productId) {
		try {
			if (db.delete("Products", "_id =" + productId, null) != 1)
				return false;

			db.delete("ProductHasParams", "ProductId = " + productId, null);
			return true;
		} catch (SQLException sqle) {
			Log.d(this.getClass().getName(), sqle.getMessage(),sqle);
			throw sqle;
		} catch (Exception e) {
			Log.d(this.getClass().getName(), e.getMessage(), e);
			return false;
		}
	}

	public Dictionary<Param, String> GetProductParams(int productId) {
		try {

			String query = "SELECT ParamId, Value" + " FROM ProductHasParams"
					+ " WHERE ProductId = " + productId;
			Cursor c = db.rawQuery(query, null);

			Dictionary<Param, String> result = new Dictionary<Param, String>();
			if (!c.moveToFirst())
				return result;
			ParamManager pm = new ParamManager(this.context);

			Dictionary<Integer, String> tmp = new Dictionary<Integer, String>();
			while (!c.isAfterLast()) {
				KeyValuePair<Integer, String> p = new KeyValuePair<Integer, String>();
				p.setKey(c.getInt(c.getColumnIndex("ParamId")));
				p.setValue(c.getString(c.getColumnIndex("Value")));
				tmp.add(p);
				c.moveToNext();
			}

			c.close();
			for (KeyValuePair<Integer, String> kpv : tmp) {
				KeyValuePair<Param, String> p = new KeyValuePair<Param, String>();
				p.setKey(pm.GetParam(kpv.getKey()));
				p.setValue(kpv.getValue());
				result.add(p);
			}

			return result;
		} catch (SQLException sqle) {
			Log.d(this.getClass().getName(), sqle.getMessage(),sqle);
			throw sqle;
		} catch (Exception e) {
			Log.d(this.getClass().getName(), e.getMessage(), e);
		}

		return null;
	}

	public boolean StoreProductParams(int productId,
			Dictionary<Param, String> paramMap) {
		if (paramMap == null)
			return false;
		if (paramMap.size() == 0)
			return true;
		
		try {
			int result = 1;
			String query = "SELECT ParamId" + " FROM ProductHasParams"
					+ " WHERE ProductId = " + productId;
			Cursor c = db.rawQuery(query, null);
			List<Integer> paramsId = new ArrayList<Integer>();
			if (c.moveToFirst()) {
				while (!c.isAfterLast()) {
					paramsId.add(c.getInt(c.getColumnIndex("ParamId")));
					c.moveToNext();
				}
			}
			List<Integer> addedParams = new ArrayList<Integer>();
			for (KeyValuePair<Param, String> kpv : paramMap) {
				addedParams.add(kpv.getKey().Id);
				boolean exists = paramsId.contains(kpv.getKey().Id);

				ContentValues cv = new ContentValues();

				cv.put("ProductId", productId);
				cv.put("ParamId", kpv.getKey().Id);
				cv.put("Value", kpv.getValue());

				if (exists)
					result = db.update("ProductHasParams", cv, "ProductId ="
							+ productId + " and ParamId =" + kpv.getKey().Id,
							null);
				else
					result = db.insert("ProductHasParams", null, cv) != -1 ? 1
							: 0;

				if (result != 1)
					throw new Exception("Failed to store product param + "
							+ kpv.getKey().Id);
			}

			for (int paramId : paramsId) {
				if (!addedParams.contains(paramId))
					try {
						if (db.delete("ProductHasParams", "ProductId ="
								+ productId + " and ParamId =" + paramId, null) <= 0)
							throw new Exception(
									"Faile to remove param row from ProductHasParams. ProductId"
											+ productId + " ParamId " + paramId);
					} catch (SQLException sqle) {
						Log.d(this.getClass().getName(), sqle.getMessage(),sqle);
						throw sqle;
					}
			}
			return result == 1 ? true : false;
		} catch (SQLException sqle) {
			Log.d(this.getClass().getName(), sqle.getMessage(), sqle);
			throw sqle;
		} catch (Exception e) {
			Log.d(this.getClass().getName(), e.getMessage(), e);
			return false;
		}
	}

	public boolean StoreProduct(Product product) {
		try {
			int result;
			ContentValues cv = new ContentValues();

			cv.put("_id", product.Id);
			cv.put("Name", product.Name);
			cv.put("TaxId", product.TaxId);
			cv.put("UnitId", product.UnitId);
			cv.put("Sku", product.Sku);
			cv.put("Description", product.Description);
			cv.put("Price", product.Price);
			cv.put("Quantity", product.Quantity);
			cv.put("MinQuantity", product.MinQuantity);
			cv.put("OrderQuantity", product.OrderQuantity);
			cv.put("Spread", product.Spread);
			cv.put("Symb", product.Symbol);
			cv.put("MarketingOnly", product.IsMarketingOnlyItem);

			int productID;
			result = db.update("Products", cv, "_id =" + product.Id, null);
			productID = product.Id;
			if (result == 0) {
				productID = (int) db.insert("Products", null, cv);
				result = productID == -1 ? -1 : 1;
			}

			if (result == -1)
				return false;

			if (!StoreProductParams(productID, product.Params))
				throw new Exception("Failed to store product params "
						+ productID);
			if (!StoreProductCategories(productID, product.Categories))
				throw new Exception("Failed to store product categories "
						+ productID);
			;
			for (ProductImage p : product.Images) {
				p.ProductId = productID;
				if (!StoreProductImage(p)) {
					throw new Exception("Failed to store product image " + p.Id);
				}
			}
			;

			return result == 1 ? true : false;
		} catch (SQLException sqle) {
			Log.d(this.getClass().getName(), sqle.getMessage(), sqle);
			throw sqle;
		} catch (Exception e) {
			Log.d(this.getClass().getName(), e.getMessage(), e);
			return false;
		}
	}

	public boolean StoreProductCategories(int productId,
			List<Category> categories) {
		if (categories == null || categories.size() == 0)
			return false;
		try {
			int result = 1;
			String query = "SELECT CategoryId" + " FROM ProductHasCategories"
					+ " WHERE ProductId = " + productId;
			Cursor c = db.rawQuery(query, null);
			List<Integer> categoryIds = new ArrayList<Integer>();
			if (c.moveToFirst()) {
				while (!c.isAfterLast()) {
					categoryIds.add(c.getInt(c.getColumnIndex("CategoryId")));
					c.moveToNext();
				}
			}
			List<Integer> addedCategories = new ArrayList<Integer>();
			for (Category cat : categories) {
				addedCategories.add(cat.Id);

				if (!categoryIds.contains(cat.Id)) {
					ContentValues cv = new ContentValues();

					cv.put("ProductId", productId);
					cv.put("CategoryId", cat.Id);

					if (db.insert("ProductHasCategories", null, cv) == -1)
						throw new Exception(
								"Faile to insert to ProductHasCategories catId: "
										+ cat.Id);
				}
			}

			for (int catId : categoryIds) {
				if (!addedCategories.contains(catId))
					try {
						if (db.delete("ProductHasCategories", "ProductId ="
								+ productId + " and CategoryId =" + catId, null) == 0)
							throw new Exception(
									"Failed to delete from ProductHasCategories ProductId: "
											+ productId + " CategoryID "
											+ catId);
					} catch (SQLException sqle) {
						Log.d(this.getClass().getName(), sqle.getMessage(),sqle);
						throw sqle;
					}
			}
			return result == 1 ? true : false;
		} catch (SQLException sqle) {
			Log.d(this.getClass().getName(), sqle.getMessage(),sqle);
			throw sqle;
		} catch (Exception e) {
			Log.d(this.getClass().getName(), e.getMessage(), e);
			return false;
		}
	}

	public List<Tax> GetTaxList() {
		try {

			String query = "SELECT _id" + " FROM Taxes";
			Cursor c = db.rawQuery(query, null);

			List<Tax> result = new ArrayList<Tax>();
			if (!c.moveToFirst())
				return result;

			List<Integer> tmp = new ArrayList<Integer>();
			while (!c.isAfterLast()) {
				tmp.add(c.getInt(c.getColumnIndex("_id")));
				c.moveToNext();
			}
			c.close();
			for (int id : tmp)
				result.add(GetTax(id));
			return result;
		} catch (SQLException sqle) {
			Log.d(this.getClass().getName(), sqle.getMessage(),sqle);
			throw sqle;
		} catch (Exception e) {
			Log.d(this.getClass().getName(), e.getMessage(), e);
		}

		return null;
	}

	public Tax GetTax(int taxId) {
		try {

			String query = "SELECT _id, Value" + " FROM Taxes"
					+ " WHERE _id = " + taxId;
			Cursor c = db.rawQuery(query, null);

			if (!c.moveToFirst())
				return null;

			Tax result = new Tax();
			result.Id = c.getInt(c.getColumnIndex("_id"));
			result.Value = c.getDouble(c.getColumnIndex("Value"));

			c.close();
			
			return result;
		} catch (SQLException sqle) {
			Log.d(this.getClass().getName(), sqle.getMessage(),sqle);
			throw sqle;
		} catch (Exception e) {
			Log.d(this.getClass().getName(), e.getMessage(), e);
		}

		return null;
	}

	public boolean StoreTax(Tax tax) {
		try {
			int result;
			ContentValues cv = new ContentValues();

			cv.put("_id", tax.Id);
			cv.put("Value", tax.Value);

			result = db.update("Taxes", cv, "_id =" + tax.Id, null);
			if (result == 0)
				result = db.insert("Taxes", null, cv) != -1 ? 1 : 0;

			return result == 1 ? true : false;
		} catch (SQLException sqle) {
			Log.d(this.getClass().getName(), sqle.getMessage(),sqle);
			throw sqle;
		} catch (Exception e) {
			Log.d(this.getClass().getName(), e.getMessage(), e);
			return false;
		}
	}

	public boolean RemoveTax(int taxId) {
		try {
			int result;
			result = db.delete("Taxes", "_id =" + taxId, null);
			return result == 1 ? true : false;
		} catch (SQLException sqle) {
			Log.d(this.getClass().getName(), sqle.getMessage(),sqle);
			throw sqle;
		} catch (Exception e) {
			Log.d(this.getClass().getName(), e.getMessage(), e);
			return false;
		}
	}

	public List<Unit> GetUnitList() {
		try {

			String query = "SELECT _id" + " FROM Units";
			Cursor c = db.rawQuery(query, null);

			List<Unit> result = new ArrayList<Unit>();
			if (!c.moveToFirst())
				return result;

			List<Integer> tmp = new ArrayList<Integer>();
			while (!c.isAfterLast()) {
				tmp.add(c.getInt(c.getColumnIndex("_id")));
				c.moveToNext();
			}
			c.close();
			for (int id : tmp)
				result.add(GetUnit(id));
			return result;
		} catch (SQLException sqle) {
			Log.d(this.getClass().getName(), sqle.getMessage(),sqle);
			throw sqle;
		} catch (Exception e) {
			Log.d(this.getClass().getName(), e.getMessage(), e);
		}

		return null;
	}

	public Unit GetUnit(int unitId) {
		try {

			String query = "SELECT _id, Name" + " FROM Units" + " WHERE _id = "
					+ unitId;
			Cursor c = db.rawQuery(query, null);

			if (!c.moveToFirst())
				return null;

			Unit result = new Unit();
			result.Id = c.getInt(c.getColumnIndex("_id"));
			result.Name = c.getString(c.getColumnIndex("Name"));

			c.close();
			
			return result;
		} catch (SQLException sqle) {
			Log.d(this.getClass().getName(), sqle.getMessage(),sqle);
			throw sqle;
		} catch (Exception e) {
			Log.d(this.getClass().getName(), e.getMessage(), e);
		}
		return null;
	}

	public boolean StoreUnit(Unit unit) {
		try {
			int result;
			ContentValues cv = new ContentValues();

			cv.put("_id", unit.Id);
			cv.put("Name", unit.Name);

			result = db.update("Units", cv, "_id =" + unit.Id, null);
			if (result == 0)
				result = db.insert("Units", null, cv) != -1 ? 1 : 0;

			return result == 1 ? true : false;
		} catch (SQLException sqle) {
			Log.d(this.getClass().getName(), sqle.getMessage(),sqle);
			throw sqle;
		} catch (Exception e) {
			Log.d(this.getClass().getName(), e.getMessage(), e);
			return false;
		}
	}

	public boolean RemoveUnit(int unitId) {
		try {
			int result;
			result = db.delete("Units", "_id =" + unitId, null);
			return result == 1 ? true : false;
		} catch (SQLException sqle) {
			Log.d(this.getClass().getName(), sqle.getMessage(),sqle);
			throw sqle;
		} catch (Exception e) {
			Log.d(this.getClass().getName(), e.getMessage(), e);
			return false;
		}
	}

	public ArrayList<Category> GetProductCategories(int productId) {
		try {

			String query = "SELECT CategoryId" + " FROM ProductHasCategories"
					+ " WHERE ProductId = " + productId;
			Cursor c = db.rawQuery(query, null);

			ArrayList<Category> result = new ArrayList<Category>();
			if (!c.moveToFirst())
				return result;

			List<Integer> tmp = new ArrayList<Integer>();
			while (!c.isAfterLast()) {
				tmp.add(c.getInt(c.getColumnIndex("CategoryId")));
				c.moveToNext();
			}
			c.close();
			for (int id : tmp)
				result.add(GetCategory(id));
			return result;
		} catch (SQLException sqle) {
			Log.d(this.getClass().getName(), sqle.getMessage(),sqle);
			throw sqle;
		} catch (Exception e) {
			Log.d(this.getClass().getName(), e.getMessage(), e);
		}
		return null;
	}

	public List<Category> GetCategoryList() {
		try {

			String query = "SELECT _id" + " FROM Categories";
			Cursor c = db.rawQuery(query, null);

			List<Category> result = new ArrayList<Category>();
			if (!c.moveToFirst())
				return result;

			List<Integer> tmp = new ArrayList<Integer>();
			while (!c.isAfterLast()) {
				tmp.add(c.getInt(c.getColumnIndex("_id")));
				c.moveToNext();
			}
			c.close();
			for (int id : tmp)
				result.add(GetCategory(id));
			return result;
		} catch (SQLException sqle) {
			Log.d(this.getClass().getName(), sqle.getMessage(),sqle);
			throw sqle;
		} catch (Exception e) {
			Log.d(this.getClass().getName(), e.getMessage(), e);
		}

		return null;
	}

	public Category GetCategory(int categoryId) {
		try {

			String query = "SELECT _id, ParentCategoryId, Name, Description"
					+ " FROM Categories" + " WHERE _id = " + categoryId;
			Cursor c = db.rawQuery(query, null);

			if (!c.moveToFirst())
				return null;

			Category result = new Category();
			result.Id = c.getInt(c.getColumnIndex("_id"));
			result.Name = c.getString(c.getColumnIndex("Name"));
			result.Description = c.getString(c.getColumnIndex("Description"));
			result.ParentId = c.getInt(c.getColumnIndex("ParentCategoryId"));

			c.close();
			return result;
		} catch (SQLException sqle) {
			Log.d(this.getClass().getName(), sqle.getMessage(),sqle);
			throw sqle;
		} catch (Exception e) {
			Log.d(this.getClass().getName(), e.getMessage(), e);
		}

		return null;
	}

	public boolean StoreCategory(Category category) {
		try {
			int result;
			ContentValues cv = new ContentValues();

			cv.put("_id", category.Id);
			cv.put("Name", category.Name);
			cv.put("Description", category.Description);
			cv.put("ParentCategoryId", category.ParentId);

			result = db.update("Categories", cv, "_id =" + category.Id, null);
			if (result == 0)
				result = db.insert("Categories", null, cv) != -1 ? 1 : 0;

			return result == 1 ? true : false;
		} catch (SQLException sqle) {
			Log.d(this.getClass().getName(), sqle.getMessage(),sqle);
			throw sqle;
		} catch (Exception e) {
			Log.d(this.getClass().getName(), e.getMessage(), e);
			return false;
		}
	}

	public boolean RemoveCategory(int categoryId) {
		try {
			int result;
			result = db.delete("Categories", "_id =" + categoryId, null);
			return result == 1 ? true : false;
		} catch (SQLException sqle) {
			Log.d(this.getClass().getName(), sqle.getMessage(),sqle);
			throw sqle;
		} catch (Exception e) {
			Log.d(this.getClass().getName(), e.getMessage(), e);
			return false;
		}
	}

	public ArrayList<ProductImage> GetProductImages(int productId) {
		try {

			String query = "SELECT _id, ProductId, Path, Position"
					+ " FROM ProductImage" + " WHERE ProductId = " + productId;
			Cursor c = db.rawQuery(query, null);

			ArrayList<ProductImage> result = new ArrayList<ProductImage>();
			if (!c.moveToFirst())
				return result;

			while (!c.isAfterLast()) {
				ProductImage p = new ProductImage();
				p.Id = c.getInt(c.getColumnIndex("_id"));
				p.ProductId = c.getInt(c.getColumnIndex("ProductId"));
				p.Path = c.getString(c.getColumnIndex("Path"));
				p.Position = c.getInt(c.getColumnIndex("Position"));

				result.add(p);
				c.moveToNext();
			}
			c.close();

			return result;
		} catch (SQLException sqle) {
			Log.d(this.getClass().getName(), sqle.getMessage(),sqle);
			throw sqle;
		} catch (Exception e) {
			Log.d(this.getClass().getName(), e.getMessage(), e);
		}

		return null;
	}

	public boolean StoreProductImage(ProductImage productImage) {
		try {
			int result;
			ContentValues cv = new ContentValues();

			cv.put("_id", productImage.Id);
			cv.put("Path", productImage.Path);
			cv.put("Position", productImage.Position);
			cv.put("ProductId", productImage.ProductId);

			result = db.update("ProductImage", cv, "_id =" + productImage.Id,
					null);
			if (result == 0)
				result = db.insert("ProductImage", null, cv) != -1 ? 1 : 0;
			return result == 1 ? true : false;
		} catch (SQLException sqle) {
			Log.d(this.getClass().getName(), sqle.getMessage(),sqle);
			throw sqle;
		} catch (Exception e) {
			Log.d(this.getClass().getName(), e.getMessage(), e);
			return false;
		}
	}

	public boolean RemoveProductImage(int productImageId) {
		try {
			int result;
			result = db.delete("ProductImage", "_id =" + productImageId, null);
			return result == 1 ? true : false;
		} catch (SQLException sqle) {
			Log.d(this.getClass().getName(), sqle.getMessage(),sqle);
			throw sqle;
		} catch (Exception e) {
			Log.d(this.getClass().getName(), e.getMessage(), e);
			return false;
		}
	}
}
